package com.tisson.view;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.tisson.database.FormatUtil;
import com.tisson.database.StatementUtil;
import com.tisson.vo.RightVO;


public class RightView {
	
	private String query;
	
	public List findAll() throws SQLException {
		query = "SELECT ac_area.AreaId,DepartmentId,ac_right.UserId,ac_right.DoorId,AreaName,DistrictName,BuildingName,DoorName,State,UserState,ExpiryDate FROM ac_right,ac_user,ac_department,"
			  + "ac_door,ac_building,ac_district,ac_area "
			  + "WHERE "
			  + "ac_right.UserId = ac_user.UserId AND "
			  + "ac_user.UserDepartment = ac_department.DepartmentId AND "
			  + "ac_right.DoorId = ac_door.DoorId AND "
			  + "ac_door.DoorBuilding = ac_building.BuildingId AND "
			  + "ac_building.DistrictId = ac_district.DistrictId AND "
			  + "ac_district.AreaId = ac_area.AreaId";
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(query);
		List list = new ArrayList();
		while (rs.next()) {
			RightVO vo = new RightVO();
			vo.setAreaId(FormatUtil.ConvertUuid(rs.getBytes("AreaId")));
			vo.setDepartmentId(FormatUtil.ConvertUuid(rs.getBytes("DepartmentId")));
			vo.setUserId(FormatUtil.ConvertUuid(rs.getBytes("UserId")));
			vo.setDoorId(FormatUtil.ConvertUuid(rs.getBytes("DoorId")));
			vo.setAreaName(rs.getString("AreaName"));
			vo.setDistrictName(rs.getString("DistrictName"));
			vo.setBuildingName(rs.getString("BuildingName"));
			vo.setDoorName(rs.getString("DoorName"));
			vo.setState(rs.getInt("State"));
			vo.setUserState(rs.getInt("UserState"));
			vo.setExpiryDate(rs.getDate("ExpiryDate"));
			list.add(vo);
		}
		rs.close();
		stmt.close();
		return list;
	}
	
	public List findByUserId(String userId) throws SQLException {
		query = "SELECT ac_area.AreaId,DepartmentId,ac_right.UserId,ac_right.DoorId,AreaName,DistrictName,BuildingName,DoorName,State,UserState,ExpiryDate FROM ac_right,ac_user,ac_department,"
			  + "ac_door,ac_building,ac_district,ac_area "
			  + "WHERE "
			  + "ac_right.UserId = '"+userId+"' AND "
			  + "ac_right.UserId = ac_user.UserId AND "
			  + "ac_user.UserDepartment = ac_department.DepartmentId AND "
			  + "ac_right.DoorId = ac_door.DoorId AND "
			  + "ac_door.DoorBuilding = ac_building.BuildingId AND "
			  + "ac_building.DistrictId = ac_district.DistrictId AND "
			  + "ac_district.AreaId = ac_area.AreaId";
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(query);
		List list = new ArrayList();
		while (rs.next()) {
			RightVO vo = new RightVO();
			vo.setAreaId(FormatUtil.ConvertUuid(rs.getBytes("AreaId")));
			vo.setDepartmentId(FormatUtil.ConvertUuid(rs.getBytes("DepartmentId")));
			vo.setUserId(FormatUtil.ConvertUuid(rs.getBytes("UserId")));
			vo.setDoorId(FormatUtil.ConvertUuid(rs.getBytes("DoorId")));
			vo.setAreaName(rs.getString("AreaName"));
			vo.setDistrictName(rs.getString("DistrictName"));
			vo.setBuildingName(rs.getString("BuildingName"));
			vo.setDoorName(rs.getString("DoorName"));
			vo.setState(rs.getInt("State"));
			vo.setUserState(rs.getInt("UserState"));
			vo.setExpiryDate(rs.getDate("ExpiryDate"));
			list.add(vo);
		}
		rs.close();
		stmt.close();
		return list;
	}
	
	public List findByDoorId(String doorId) throws SQLException {
		query = "SELECT ac_area.AreaId,DepartmentId,ac_right.UserId,ac_right.DoorId,AreaName,DistrictName,BuildingName,DoorName,State,UserState,ExpiryDate"
			  +" FROM ac_right,ac_user,ac_department,ac_door,ac_building,ac_district,ac_area "
			  + "WHERE "
			  + "ac_right.DoorId = '"+doorId+"' AND "
			  + "ac_right.UserId = ac_user.UserId AND "
			  + "ac_user.UserDepartment = ac_department.DepartmentId AND "
			  + "ac_right.DoorId = ac_door.DoorId AND "
			  + "ac_door.DoorBuilding = ac_building.BuildingId AND "
			  + "ac_building.DistrictId = ac_district.DistrictId AND "
			  + "ac_district.AreaId = ac_area.AreaId";
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(query);
		List list = new ArrayList();
		while (rs.next()) {
			RightVO vo = new RightVO();
			vo.setAreaId(FormatUtil.ConvertUuid(rs.getBytes("AreaId")));
			vo.setDepartmentId(FormatUtil.ConvertUuid(rs.getBytes("DepartmentId")));
			vo.setUserId(FormatUtil.ConvertUuid(rs.getBytes("UserId")));
			vo.setDoorId(FormatUtil.ConvertUuid(rs.getBytes("DoorId")));
			vo.setAreaName(rs.getString("AreaName"));
			vo.setDistrictName(rs.getString("DistrictName"));
			vo.setBuildingName(rs.getString("BuildingName"));
			vo.setDoorName(rs.getString("DoorName"));
			vo.setState(rs.getInt("State"));
			vo.setUserState(rs.getInt("UserState"));
			vo.setExpiryDate(rs.getDate("ExpiryDate"));
			list.add(vo);
		}
		rs.close();
		stmt.close();
		return list;
	}

}
